﻿//Copyright@<2009> <Naveen>

using System;
using System.Data;
using MySql.Data.MySqlClient;
using System.Xml;



namespace ProjectMain
{
    class DBClass     // make dbconnection class
    {
        //    public static void DBConnAndUpdate(string username, string password, string firstname, string lastname)
        public static void DBConnAndUpdate(params string[] v)
        {
            string username = v[0];
            string password = v[1];
            string firstname = v[2];
            string lastname = v[3];

            // database connection class





            MySqlConnection dbcon = ConnectToDbClass.ConnectDb();

            dbcon.Open();

            MySqlDataAdapter DataAdapter = new MySqlDataAdapter("select * from AccountValidation", dbcon);

            DataSet myDataSet = new DataSet();

            MySqlCommandBuilder CommandBuilder = new MySqlCommandBuilder(DataAdapter);

            DataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;

            DataAdapter.Fill(myDataSet, "AccountValidation");

            DataRow myDataRow = myDataSet.Tables["AccountValidation"].NewRow();
            myDataRow["username"] = username;
            myDataRow["password"] = password;
            myDataRow["firstname"] = firstname;
            myDataRow["lastname"] = lastname;

            myDataSet.Tables["AccountValidation"].Rows.Add(myDataRow);


            DataAdapter.Update(myDataSet, "AccountValidation");

            Console.WriteLine("\nYour account has been created successfully!!\n");

            //foreach (DataRow myDataR in myDataSet.Tables["AccountValidation"].Rows)
            //{
            //    Console.WriteLine(myDataR["username"]);
            //     Add confirmation logic and note that a/c has been created successfully

            //}

            // clean up
            dbcon.Close();
            dbcon = null;
        }





        public static void DBConnAndGetData(string username, string password, out string firstname, out string lastname)
        {

            // database connection class


          
	

            MySqlConnection dbcon = ConnectToDbClass.ConnectDb();


            dbcon.Open();

            // string Query =  string.Format("select * from AccountValidation where username ={0} and password ={1};",username, password);

            string Query = string.Format("select * from AccountValidation;");

            MySqlDataAdapter DataAdapter = new MySqlDataAdapter(Query, dbcon);

            DataSet myDataSet = new DataSet();

            MySqlCommandBuilder CommandBuilder = new MySqlCommandBuilder(DataAdapter);

            DataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;

            DataAdapter.Fill(myDataSet, "AccountValidation");


            DataRow myDataRow = myDataSet.Tables["AccountValidation"].Rows.Find(username);
            myDataRow["password"] = password;

            firstname = myDataRow["firstname"].ToString();
            lastname = myDataRow["lastname"].ToString();

            //   DataAdapter.Update(myDataSet, "AccountValidation");

            // Console.WriteLine("Your account has been created successfully!!");

            // foreach (DataRow myDataR in myDataSet.Tables["AccountValidation"].Rows)
            // {
            //     Console.WriteLine(myDataR["firstname"]);
            // //     Add confirmation logic and note that a/c has been created successfully

            //}

            // clean up
            dbcon.Close();
            dbcon = null;
        }









        public static void DBConnAndAddVacations(string username, DateTime startdate, DateTime enddate)
        {

            // database connection class

                     
	

            MySqlConnection dbcon = ConnectToDbClass.ConnectDb();
            dbcon.Open();

            //prepare Command String

            string UpdateString = string.Format("insert into VacationManager values ('{0}', '{1}','{2}')", username, startdate, enddate);


            //Instantiate the command witha query and connection
            MySqlCommand cmd = new MySqlCommand(UpdateString, dbcon);

            // object _retVal = cmd.ExecuteScalar();

            // Call ExecuteNonQuery 
            int count = (int)cmd.ExecuteNonQuery();

            dbcon.Close();

            //Console.ReadLine();

        }








//        public static void DBConnAndAddVacations(string username, DateTime startdate, DateTime enddate)
//        {


            // database connection class


         
//	string connectionString =
//               "Server=db.cecs.pdx.edu;" +
//               "Database=nkumar;" +
//               "User ID=nkumar;" +
//              "Password=p7Wjnrm5?b;" +
//               "Pooling=false";

//            MySqlConnection dbcon = new MySqlConnection(connectionString);

//            dbcon.Open();

//            MySqlDataAdapter DataAdapter = new MySqlDataAdapter("select * from VacationManager;", dbcon);

//            DataSet myDataSet = new DataSet();

//            MySqlCommandBuilder CommandBuilder = new MySqlCommandBuilder(DataAdapter);

//            DataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;

//            DataAdapter.Fill(myDataSet, "VacationManager");

//            DataRow myDataRow = myDataSet.Tables["VacationManager"].NewRow();
//            myDataRow["username"] = username;
//            myDataRow["startdate"] = startdate;
//            myDataRow["enddate"] = enddate;

//            myDataSet.Tables["VacationManager"].Rows.Add(myDataRow);


//            DataAdapter.Update(myDataSet, "VacationManager");

//            Console.WriteLine("Your vacation's start and end date has been created successfully!!");

            //foreach (DataRow myDataR in myDataSet.Tables["AccountValidation"].Rows)
            //{
            //    Console.WriteLine(myDataR["username"]);
            //     Add confirmation logic and note that a/c has been created successfully

            //}

            // clean up
//            dbcon.Close();
//            dbcon = null;
//        }










        public static void DBConnAndModifyVacation(string username, string startdate, string enddate, string newstartdate, string newenddate)
        {

            // database connection class
                     
	        MySqlConnection dbcon = ConnectToDbClass.ConnectDb();
            dbcon.Open();

            //prepare Command String

            string UpdateString = string.Format("update VacationManager set startdate = '{0}', enddate=  '{1}' where username = '{2}' and startdate = '{3}' and enddate = '{4}'", newstartdate, newenddate, username, startdate, enddate);


            //Instantiate the command witha query and connection
            MySqlCommand cmd = new MySqlCommand(UpdateString, dbcon);

            // object _retVal = cmd.ExecuteScalar();

            // Call ExecuteNonQuery 
            int count = (int)cmd.ExecuteNonQuery();

            dbcon.Close();

            //Console.ReadLine();

        }








        public static void DBConnAndViewAllVacations()
        {

            // database connection class

         
	string connectionString =
               "Server=db.cecs.pdx.edu;" +
               "Database=nkumar;" +
               "User ID=nkumar;" +
               "Password=p7Wjnrm5?b;" +
               "Pooling=false";

            MySqlConnection dbcon = new MySqlConnection(connectionString);

            dbcon.Open();

            //prepare Command String

            string viewall = string.Format("SELECT * FROM VacationManager");

            //Instantiate the command witha query and connection
            MySqlCommand cmd = new MySqlCommand(viewall, dbcon);

            //Call Execute reader to get query results
            MySqlDataReader results = null;

            results = cmd.ExecuteReader();

            Console.WriteLine("------------------------------------------------------------------------------");
            string zz1 = String.Format("|  {0,10}  |  {1,20}  |  {2,30}  |", "Username", "Start Date", "End Date");
            Console.WriteLine(zz1);
            Console.WriteLine("------------------------------------------------------------------------------");

            while (results.Read())
            {
                string zz = String.Format("|  {0,10}  |  {1,20}  |  {2,30}  |", results[0], results[1], results[2]);
                Console.WriteLine(zz);
            }

            Console.WriteLine("------------------------------------------------------------------------------");

            dbcon.Close();

        }



            

        public static void DBConnAndViewYourVacations(string username)
        {

            // database connection class

	        MySqlConnection dbcon = ConnectToDbClass.ConnectDb();
            dbcon.Open();

            //prepare Command String

            string viewyourvacations = string.Format("select * from VacationManager where username = '{0}'",  username);


            //Instantiate the command witha query and connection
            MySqlCommand cmd = new MySqlCommand(viewyourvacations, dbcon);

            //Call Execute reader to get query results
            MySqlDataReader results = null;

            results = cmd.ExecuteReader();

            Console.WriteLine("------------------------------------------------------------------------------");
            string zz1 = String.Format("|  {0,10}  |  {1,20}  |  {2,30}  |", "Username", "Start Date", "End Date");
            Console.WriteLine(zz1);
            Console.WriteLine("------------------------------------------------------------------------------");

            while (results.Read())
            {
                string zz = String.Format("|  {0,10}  |  {1,20}  |  {2,30}  |", results[0], results[1], results[2]);
                Console.WriteLine(zz);
            }

            Console.WriteLine("------------------------------------------------------------------------------");

            dbcon.Close();

        }





    }

}

